﻿using Dapper;
using Infrastructure.DataBaseConnect;
using Infrastructure.IRepository.Sys;
using Model;
using Model.BusinessModel;
using Model.BusinessModel.OutPut;
using Model.BusinessModel.OutPut.sys;
using Model.DomainModel.Sys;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Infrastructure.Repository.Sys
{
    /// <summary>
    /// 部门仓储接口实现
    /// </summary>
    public class SysDepartmentRepository : Repository<SysDepartment>, ISysDepartmentRepository
    {
        /// <summary>
        /// 根据名称获取部门信息
        /// </summary>
        /// <param name="corporationKey">公司key</param>
        /// <param name="name">部门名称</param>
        /// <returns></returns>
        public SysDepartment? GetSysDepartmentByName(string corporationKey, string name)
        {
            string sql = @" select * from Sys_Department where DepartmentName =@DepartmentName and CorporationKey=@CorporationKey  ";
            using var connection = DataBaseConnectConfig.GetSqlConnection();
            return connection.QueryFirstOrDefault<SysDepartment>(sql, new { CorporationKey = @corporationKey, DepartmentName = name });
        }

        /// <summary>
        /// 根据公司获取数据
        /// </summary>
        /// <param name="corporationKey">公司key</param>
        /// <returns></returns>
        public List<SysDepartment> GetDepartmentByCorporationKey(string corporationKey)
        {
            string sql = @" select * from Sys_Department where CorporationKey=@CorporationKey ";
            using var connection = DataBaseConnectConfig.GetSqlConnection();
            return connection.Query<SysDepartment>(sql, new { CorporationKey = corporationKey }).ToList();
        }

        /// <summary>
        /// 获取公司下的部门
        /// </summary>
        /// <returns></returns>
        public List<TreeOutPut> GetCorporationDepartmentList(string corporationKey)
        {
            StringBuilder sql = new(@" select * from (
                                select Id as [Key],case WHEN Pid='0' THEN  CONVERT(VARCHAR(36), CorporationKey) else CONVERT(VARCHAR(36), Pid) end AS [PKey]  ,DepartmentName as  label,CorporationKey,3 as nodeType
                                from  Sys_Department 
                                UNION 
                                SELECT CONVERT(VARCHAR(36), CorporationKey)  as [Key], '' as  [PKey] , CorporationName as  [Value],CorporationKey,1 as nodeType
                                 from Sys_Corporation) as t");

            var qp = new QueryParameter();
            if (!string.IsNullOrWhiteSpace(corporationKey))
            {
                qp.listWhere.Append(" and t.CorporationKey = @CorporationKey ");
                qp.dynamicParameter.Add("@CorporationKey", corporationKey);
            }

            if (!string.IsNullOrEmpty(qp.listWhere.ToString()))
            {
                sql.Append(" WHERE 1=1 " + qp.listWhere.ToString());
            }
            using var connection = DataBaseConnectConfig.GetSqlConnection();
            return connection.Query<TreeOutPut>(sql.ToString(), qp.dynamicParameter).ToList();
        }

        /// <summary>
        /// 获取所有公司及部门--登录公司为星辰公司，可以获取全部，否则只获取登录公司数据
        /// </summary>
        /// <returns></returns>
        public List<TreeOutPut> GetAllCorporationDepartmentList(string corporationKey)
        {
            StringBuilder sql = new(@" select * from (
                                select Id as [Key],case WHEN Pid='0' THEN  CONVERT(VARCHAR(36), CorporationKey) else CONVERT(VARCHAR(36), Pid) end AS [PKey]  ,DepartmentName as  label,CorporationKey
                                from  Sys_Department 
                                UNION 
                                SELECT CONVERT(VARCHAR(36), CorporationKey)  as [Key], '' as  [PKey] , CorporationName as  [Value],CorporationKey
                                 from Sys_Corporation) as t");

            var qp = new QueryParameter();
            if (!string.IsNullOrWhiteSpace(corporationKey) && Const.XC_CorporationKey != corporationKey)
            {
                qp.listWhere.Append(" and t.CorporationKey = @CorporationKey ");
                qp.dynamicParameter.Add("@CorporationKey", corporationKey);
            }

            if (!string.IsNullOrEmpty(qp.listWhere.ToString()))
            {
                sql.Append(" WHERE 1=1 " + qp.listWhere.ToString());
            }
            using var connection = DataBaseConnectConfig.GetSqlConnection();
            return connection.Query<TreeOutPut>(sql.ToString(), qp.dynamicParameter).ToList();
        }

        /// <summary>
        /// 根据id删除部门
        /// </summary>
        /// <param name="ids">部门id集合</param>
        public void DeleteDepartmentById(List<string> ids)
        {
            string sql = " delete from Sys_Department where Id in @Key ";
            using var connection = DataBaseConnectConfig.GetSqlConnection();
            connection.Execute(sql, new { Key = ids });
        }

        /// <summary>
        /// 根据id获取部门下的人员
        /// </summary>
        /// <param name="ids">部门Id</param>
        /// <param name="corporationKey">公司key</param>
        /// <returns></returns>
        public List<SysDepartmentOutPut> GetDepartmentById(List<string>? ids, string? corporationKey)
        {
            StringBuilder sql = new(@" select                  
                            su.UserId,su.UserName,su.Password,su.Age,su.Sex,su.IsOpen,sc.CorporationName,sc.CorporationKey,sd.DepartmentName,su.CreateTime,su.DepartmentId from Sys_User as su
                            inner join Sys_Corporation as sc on su.CorporationKey = sc.CorporationKey
                            inner join Sys_Department as sd on su.DepartmentId = sd.Id
                             ");
            var qp = new QueryParameter();
            if (!string.IsNullOrWhiteSpace(corporationKey))
            {
                qp.listWhere.Append(" and sc.CorporationKey = @CorporationKey ");
                qp.dynamicParameter.Add("@CorporationKey", corporationKey);
            }
            if (ids != null && ids.Count > 0)
            {
                qp.listWhere.Append(" and (su.DepartmentId in @Key  or sc.CorporationKey in @Key )");
                qp.dynamicParameter.Add("@Key", ids);
            }
            if (!string.IsNullOrEmpty(qp.listWhere.ToString()))
            {
                sql.Append(" WHERE 1=1 " + qp.listWhere.ToString());
            }
            using var connection = DataBaseConnectConfig.GetSqlConnection();
            return connection.Query<SysDepartmentOutPut>(sql.ToString(), qp.dynamicParameter).ToList();
        }
    }
}
